package utility;

use 5.012002;
use strict;
use warnings;
use DBI;
use Cwd;
use Statistics::Descriptive;
use Statistics::R;
use Template;

#require Exporter;

#our @ISA = qw(Exporter);

# Items to export into callers namespace by default. Note: do not export
# names by default without a very good reason. Use EXPORT_OK instead.
# Do not simply export all your public functions/methods/constants.

# This allows declaration	use utility ':all';
# If you do not need this, moving things directly into @EXPORT or @EXPORT_OK
# will save memory.
#our %EXPORT_TAGS = ( 'all' => [ qw(

#) ] );

#our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );

#our @EXPORT = qw(

#);

our $VERSION = '0.01';

# Preloaded methods go here.

sub new {
	my $package = shift;
	return bless( {}, $package );

}

my %DB_name = (
	"carmd-app16" => {
		"DB_main"      => "mfgt_db_Master",
		"DB_reporting" => "mfgt_db_Reporting"
	},

	"carmd-app28" => {
		"DB_main"      => "mfgt_db_Lab",
		"DB_reporting" => "mfgt_db_Reporting"
	},

	"cnszu-app01" => {
		"DB_main"      => "mfgt_db_Production",
		"DB_reporting" => "mfgt_db_Reporting"
	}
);

sub get_DB_connectionInfo {
	my $self            = shift;
	my $DBsourceNameRef = shift;
	my $DBTypeRef       = shift;

	my $DBServerName = get_DBServerName( undef, $DBsourceNameRef, $DBTypeRef );

	my $DBname = get_DB_name( undef, \$DBServerName, $DBTypeRef );
	my ( $DBusername, $DBpassword ) =
	  get_DBServerLoginInfo( undef, \$DBServerName );

	return ( $DBServerName, $DBname, $DBusername, $DBpassword );

}

sub get_DB_name {
	my $self      = shift;
	my $serverRef = shift;
	my $DBTypeRef = shift;

	my %DB_name = (
		"carmd-app16" => {
			"main"   => "mfgt_db_Master",
			"report" => "mfgt_db_Reporting"
		},

		"carmd-app28" => {
			"main"   => "mfgt_db_Lab",
			"report" => "mfgt_db_Reporting"
		},

		"cnszu-app01" => {
			"main"   => "mfgt_db_Production",
			"report" => undef
		},

		"cnszu-app02" => {
			"main"   => undef,
			"report" => "mfgt_db_Reporting"
		}
	);
	return $DB_name{$$serverRef}->{$$DBTypeRef};
}

sub get_DBServerName {
	my $self      = shift;
	my $serverRef = shift;
	my $tableRef  = shift;
	my %DB_Server = (
		"LabDataInRichmond" => {
			"main"   => "carmd-app28",
			"report" => "carmd-app28"
		},

		"FactoryDataInSuzhou" => {
			"main"   => "cnszu-app01",
			"report" => "cnszu-app02"
		}
	);
	return $DB_Server{$$serverRef}{$$tableRef};
}

sub get_DBServerLoginInfo {
	my $self        = shift;
	my $DBServerRef = shift;

	my %DB_Server_LoginInfo = (
		"carmd-app28" => {
			"username" => "factorytest",
			"password" => "MfgTest#2"
		},
		"carmd-app16" => {
			"username" => "factorytest",
			"password" => "MfgTest#2"
		},
		"cnszu-app01" => {
			"username" => "factorytest",
			"password" => "MfgTest#2"
		},
		"cnszu-app02" => {
			"username" => "factorytest",
			"password" => "MfgTest#2"
		}
	);

	my $username = $DB_Server_LoginInfo{$$DBServerRef}->{"username"};
	my $password = $DB_Server_LoginInfo{$$DBServerRef}->{"password"};

	return $username, $password;
}

# sub get_tableList {
# 	my $self      = shift;
#	my $DBTypeRef = shift;
#
#	my @tableListOnMainDB = [
#		"TBL_SFT_GSM_ALLB_ALLC_DATA",  "TBL_SFT_UMTS_2100_ALLC_DATA",
#		"TBL_SFT_UMTS_1900_ALLC_DATA", "TBL_SFT_UMTS_900_ALLC_DATA",
#		"TBL_SFT_UMTS_850_ALLC_DATA" ];
#	my @tableListOnReportDB = [    # "Tbl_SFT_Meas_LTE_RedSig_Rx_BLERSearch",
#		                           # "Tbl_SFT_Meas_LTE_RedSig_Rx_RSSI",
#		                           # "Tbl_SFT_Meas_LTE_RedSig_Rx_Throughput",
#		                           # "Tbl_SFT_Meas_LTE_RedSig_Tx_AtMaxPower",
#		"Tbl_RIL_BaseBand",
#		"Tbl_RIL_GSM",
#		"Tbl_RIL_UMTS" ];
#	my %tableList = (
#		"main"   => @tableListOnMainDB,
#		"report" => @tableListOnReportDB,
#	);
#	return $tableList{$DBTypeRef};
# }

sub get_tablesBySTNType {
	my $self       = shift;
	my $sourceRef  = shift;
	my $STNTypeRef = shift;

	my $main 	= "main";
	my $report	= "report";
	my $flatType = "flat";
	my $nonFlatType = "nonFlat";
	
	my $keyDBServer = "DBServer";
	my $keyDBName	= "DBName";
	my $keyTableType = "tableType";

	my %connInfo = (
		"LabDataInRichmond"	=> {$main  =>{	$keyDBServer=> "carmd-app28",
											$keyDBName	=> "mfgt_db_Lab" },
								$report=>{	$keyDBServer=> "carmd-app28",
											$keyDBName	=> "mfgt_db_Reporting"}},
								
		"FactoryDataInSuzhou"=>{$main  =>{	$keyDBServer => "cnszu-app01",
											$keyDBName	=> "mfgt_db_Production"},
								$report=>{	$keyDBServer  => "cnszu-app02",
											$keyDBName	=> "mfgt_db_Reporting"}} );
	
	my $DBServer_main 	= $connInfo{$$sourceRef}{$main}{$keyDBServer};
	my $DBname_main		= $connInfo{$$sourceRef}{$main}{$keyDBName};
	my $DBServer_report	= $connInfo{$$sourceRef}{$report}{$keyDBServer};
	my $DBname_report	= $connInfo{$$sourceRef}{$report}{$keyDBName};

	my %tables = (
		"CLT" => {},
		"RIL" => {	"Tbl_RIL_BaseBand" 	=> {$keyTableType	=> $flatType,
											$keyDBServer	=> $DBServer_report,
											$keyDBName		=> $DBname_report},
		
					"Tbl_RIL_GSM"		=> {$keyTableType	=> $flatType,
											$keyDBServer	=> $DBServer_report,
											$keyDBName		=> $DBname_report
					},
					"Tbl_RIL_UMTS"		=> {$keyTableType => $flatType,
											$keyDBServer	=> $DBServer_report,
											$keyDBName		=> $DBname_report
					} 
				},

		"RSFT" => {},
		
		"SFT"  => {	"Tbl_SFT_Meas_LTE_RedSig_Rx_BLERSearch" => {$keyTableType  	=> $nonFlatType,
																$keyDBServer	=> $DBServer_report,
																$keyDBName		=> $DBname_report
					},
					"Tbl_SFT_Meas_LTE_RedSig_Rx_RSSI" 		=> {$keyTableType  	=> $nonFlatType,
																$keyDBServer	=> $DBServer_report,
																$keyDBName		=> $DBname_report
					},
					"Tbl_SFT_Meas_LTE_RedSig_Rx_Throughput" => {$keyTableType  	=> $nonFlatType,
																$keyDBServer	=> $DBServer_report,
																$keyDBName		=> $DBname_report
					},
					"Tbl_SFT_Meas_LTE_RedSig_Tx_AtMaxPower" => {$keyTableType	=> $nonFlatType,
																$keyDBServer	=> $DBServer_report,
																$keyDBName		=> $DBname_report
					} 
				},

		"XCOMBO" => { 	"TBL_SFT_GSM_ALLB_ALLC_DATA"	=> {$keyTableType  	=> $flatType,
															$keyDBServer	=> $DBServer_main,
															$keyDBName		=> $DBname_main
															},
						"TBL_SFT_UMTS_2100_ALLC_DATA"	=> {$keyTableType  	=> $flatType,
															$keyDBServer	=> $DBServer_main,
															$keyDBName		=> $DBname_main
															},
						"TBL_SFT_UMTS_1900_ALLC_DATA"	=> {$keyTableType  	=> $flatType,
															$keyDBServer	=> $DBServer_main,
															$keyDBName		=> $DBname_main
															},
						"TBL_SFT_UMTS_900_ALLC_DATA"	=> {$keyTableType  	=> $flatType,
															$keyDBServer	=> $DBServer_main,
															$keyDBName		=> $DBname_main
															},
						"TBL_SFT_UMTS_850_ALLC_DATA" 	=> {$keyTableType  => $flatType,
															$keyDBServer	=> $DBServer_main,
															$keyDBName		=> $DBname_main
															} 
					}
	);
	
	return $tables{$$STNTypeRef};
}

sub get_NonFlattableList {
	my $self      = shift;
	my $DBTypeRef = shift;

	my @tableListOnMainDB = [undef];

	my @tableListOnReportDB = [
		"Tbl_SFT_Meas_LTE_RedSig_Rx_BLERSearch",
		"Tbl_SFT_Meas_LTE_RedSig_Rx_RSSI",
		"Tbl_SFT_Meas_LTE_RedSig_Rx_Throughput",
		"Tbl_SFT_Meas_LTE_RedSig_Tx_AtMaxPower"
	];

	my %tableList = (
		"main"   => @tableListOnMainDB,
		"report" => @tableListOnReportDB,
	);

	return $tableList{$$DBTypeRef};

}

sub getRunIDColumnName {
	my $self     = shift;
	my $tableRef = shift;

	my %runIDColNameByTable = (    # from main DB
		"TBL_SFT_GSM_ALLB_ALLC_DATA"  => "G_Test_RunID",
		"TBL_SFT_UMTS_2100_ALLC_DATA" => "G_Test_RunID",
		"TBL_SFT_UMTS_1900_ALLC_DATA" => "G_Test_RunID",
		"TBL_SFT_UMTS_900_ALLC_DATA"  => "G_Test_RunID",
		"TBL_SFT_UMTS_850_ALLC_DATA"  => "G_Test_RunID",

		# from reporting DB
		"Tbl_RIL_BaseBand" => "RunID",
		"Tbl_RIL_GSM"      => "RunID",
		"Tbl_RIL_UMTS"     => "RunID"
	);
	return $runIDColNameByTable{$$tableRef};
}

sub logmsg {
	my $self = shift;

	my $txt_ref = shift;
	my $logmsg  = shift;

	
	# print "$logmsg \n";
	return undef unless defined $txt_ref;

	if ($logmsg =~ /^.$/) {
		print "$logmsg";
		$txt_ref->insert( 'end', "$logmsg" );
	}else
	{
		print "$logmsg \n";
		$txt_ref->insert( 'end', "$logmsg \n" );	
	}
		
	$txt_ref->see('end');
}

sub run_query {
	my $self     = shift;
	my $dbh      = shift;
	my $sql_stmt = shift;

	# selectall_arrayref returns a bidimensional array: columns by rows
	return $dbh->selectall_arrayref($sql_stmt);
}

sub run_query_hash {
	my $self     = shift;
	my $dbh      = shift;
	my $sql_stmt = shift;

	# selectall_arrayref returns a bidimensional array: columns by rows
	# return $dbh->selectall_arrayref($sql_stmt, { Slice => {} });
	my $arrayRef = $dbh->selectall_arrayref( $sql_stmt, { Slice => {} } );

	return $arrayRef;
}

sub run_query_selectrow_hashref {
	my $self     = shift;
	my $dbh      = shift;
	my $sql_stmt = shift;

	# selectall_arrayref returns a bidimensional array: columns by rows
	#return $dbh->selectall_arrayref($sql_stmt, { Slice => {} });
	my $arrayRef = $dbh->selectrow_hashref($sql_stmt);

	return $arrayRef;
}

sub connect_to_db_server_mssql {
	my $self = shift;

	# connect to db and get all the available SW rev.
	my $server_name = shift;
	my $db_name     = shift;
	my $username    = shift;
	my $password    = shift;

	my $dbh;

	# Connect to the the database server: no need for user name or password as it is
	# 	running on Win32 with Active Directory authentication
	
	(
		$dbh = DBI->connect("DBI:ODBC:Driver={SQL Server};Server=$server_name;Database=$db_name",
			$username,
			$password
		)
	) or croak("$DBI::errstr\n");

	# save having to check each method call
	$dbh->{'RaiseError'} = 1;

	# the NV log file is approximately 20 KBytes long, hence need to
	# resize LongReadLen (defined by DBI to be 16384).
	#$dbh->{'LongReadLen'} = 65536;
	$dbh->{'LongReadLen'} = 50000000;

	return $dbh;
}

####################################################
#
#	Input: 	$DataSetRef
#				- It's a Array of Array. Each elements include the following array.
#				  [0] Column ID, [1] Limit file path [2] Data source file path
####################################################
sub GenRChart_Boxplot {
	my $self         = shift;
	my $filesHoARef  = shift;
	my $OutPutDirRef = shift;
	my $IsvariableType	= shift;
	my $ReturnFromR;
	my $RCmd;

	chdir("$$OutPutDirRef");
	my $dir = getcwd;
	print "current work directory = $dir, \n\n";

	my $R = Statistics::R->new();
	$R->startR;
	
	foreach ( keys %{$filesHoARef} ) {
		$RCmd = GenRScript_BoxPlot(
			\$filesHoARef->{$_}->[2],
			$OutPutDirRef,    # Current working directory.
			\$filesHoARef->{$_}->[0],
			\$filesHoARef->{$_}->[1],
			$IsvariableType		
		);

		# $R->startR;
		print "Sending R Script: \n $RCmd \n";
		$R->send(qq($RCmd));

		my $errR = $R->error;
		print "Error from R: $errR \n" if defined $errR;

		# $R->clean_up();	# Bug? the perl will hangs.
		$ReturnFromR = $R->read;
		print "Error from R: $ReturnFromR \n" if defined $errR;

		# $R->stopR();
	}

	$R->stopR();
}

##################################################
#
####################################################
sub GenRScript_BoxPlot {
	my $nameRef         = shift;
	my $OutPutDirRef    = shift;
	my $DataFilePathRef = shift;
	my $PNGFilePathRef  = shift;
	my $IsXaxisVarRef	= shift;

	my $RCmd;

	$RCmd = sprintf "library(plotrix)
				graphics.off()
				png(filename=\"%s\",width = 450, height = 300)
				# read source data
				datasource<-read.table(\"%s\",header=TRUE)
				attach(datasource)
				par(bg=\"lightsteelblue\")
				boxplot(value ~ group, data=datasource,show.names=%s,col=rainbow(length(levels(group))))
				# boxplot(value ~ group, data=datasource,col=rainbow(length(levels(group))))
				title(main=\"%s\", font.main=4, font.lab=1)
				# Why is this?
				%s
				dev.off();
				# clean some objects
				detach(datasource)
				rm(datasource)
				rm()",
				$$PNGFilePathRef,
				$$DataFilePathRef,
				$$IsXaxisVarRef?"TRUE":"FALSE",
				$$nameRef,
				# $$IsXaxisVarRef?"staxlab(side=1, group+1, group,srt=45)":"staxlab(side=1, group, group,srt=45)";
				$$IsXaxisVarRef?"":"staxlab(side=1, group, group,srt=45)";
			

	return $RCmd;
}

####################################################
#
#	Input: 	$DataSetRef
#				- It's a Array of Array. Each elements include the following array.
#				  [0] Column ID, [1] Limit file path [2] Data source file path
####################################################
sub GenRChart_Pareto {
	my $self           = shift;
	my $OutPutDirRef   = shift;
	my $DataFileRef    = shift;
	my $PNGFilePathRef = shift;
	my $IsXaxisVarRef	= shift;

	my $ReturnFromR;
	my $RCmd;

	chdir("$$OutPutDirRef");
	my $dir = getcwd;
	# print "current work directory= $dir, \n\n";

	my $R = Statistics::R->new();
	$R->startR;

	# where to save the PNG file.

	$RCmd = GenRScript_Pareto(

		#$CharttypeRef, 	# Chart type. Pareto Chart, Process Capability chart.
		$OutPutDirRef,     # Current working directory.
		$DataFileRef,      # Data file path.
		$PNGFilePathRef    # PNG file path
	);

	print "Sending R Script: \n $RCmd \n";
	$R->send(qq($RCmd));

	# my $errR = $R->error;
	# print "Error from R: $errR \n";

	# $R->clean_up();	# Bug? the perl will hangs.

	$ReturnFromR = $R->read;
	
	
	if (defined $ReturnFromR) {
		
		print "R Script returns: $ReturnFromR \n";	
	}
	
	$R->stopR();
}

##################################################
#
####################################################
sub GenRScript_Pareto {

	my $OutPutDirRef    = shift;
	my $DataFilePathRef = shift;
	my $PNGFilePathRef  = shift;

	my $RCmd;
	$RCmd = sprintf "# library(plotrix)
				library(qcc)
				graphics.off()
				setwd(\"%s\")
				png(filename=\"%s\",width = 450, height = 300)
				datasource<-read.table(\"%s\",header=TRUE)
				attach(datasource)
				defect<-Frequency
				names(defect)<-ErrorCode
				par(bg=\"lightsteelblue\")
				pareto.chart(defect, col=rainbow(length(defect)))
				# show.names=FALSE,
				# staxlab(side=1,ErrorCode, ErrorCode,srt=45)
				
				dev.off()
				# Clean objects. must be cleaned otherwise the subsquent script won't work properly.
				# it only generate first chart, rest of them are empty image.
				rm(qcc)
				rm(datasource)
				rm(defect)",
				$$OutPutDirRef,
				$$PNGFilePathRef,
				$$DataFilePathRef;
	return $RCmd;

}

####################################################
#
#	Input: 	$DataSetRef
#				- It's a Array of Array. Each elements include the following array.
#				  [0] Column ID, [1] Limit file path [2] Data source file path
####################################################
sub GenRChart_ProcessCapability {
	my $self         = shift;
	my $op_textRef   = shift;
	my $OutPutDirRef = shift;
	my $DataSetRef   = shift;

	my $ReturnFromR;
	my $RCmd;

	chdir("$$OutPutDirRef");
	my $dir = getcwd;
	# print "current work directory= $dir, \n\n";

	my $R = Statistics::R->new();
	$R->startR;

	for my $aref (@$DataSetRef) {
		# my ( $limitfile, $outputPath_limit ) = fileparse($aref->[1]);
		# my ( $datafile, $outputPath_data ) = fileparse($aref->[2]);
		# logmsg( undef, $op_textRef,
		#	"Processing: \n Limit file: $limitfile) \n Data file: $datafile ) \n"
		# );
		logmsg( undef, $op_textRef, ".");
		$RCmd = GenRScript_ProcCapability(
			$OutPutDirRef,    # Current working directory.
			\$aref->[1],      # Limit file path
			\$aref->[2],      # Data file path.
			\$aref->[3]       # PNG file path
		);
		$R->send(qq($RCmd));

		my $ret = $R->read(5);
		# logmsg( undef, $op_textRef, "output from R: $ret" );
	}
	my $err = $R->error();
	$R->stopR();

	return $err;
}

##################################################
#
####################################################
sub GenRScript_ProcCapability {

	# my $CharttypeRef = shift;
	my $OutPutDirRef = shift;

	my $LimitFilePathRef = shift;
	my $DataFilePathRef  = shift;
	my $PNGFilePathRef   = shift;

	my $RCmd;

	$RCmd = "	# Clean working environment. get rid of leftover
					# rm(list=ls())
					library(qcc)
					graphics.off()
				   	# setwd(\"$$OutPutDirRef\")
					png(filename=\"$$PNGFilePathRef\",width = 450, height = 300)
					#png(filename=\"./DynamicWebPage/ProcessCapabilityChart.png\",width = 450, height = 300)
					limits<-read.table(\"$$LimitFilePathRef\", header=TRUE,na.strings=\"NA\")
					attach(limits)
					if (is.na(t)) t=NULL
					datasource<-read.table(\"$$DataFilePathRef\", header=TRUE, na.strings=\"NA\");
					attach(datasource)	
					ChartTitle<-names(datasource[1])
					obj<-qcc( datasource,type=\"xbar.one\",target=t,data.name=names(datasource[1]))
					qcc.options(font.stats = 1, cex.stats = 0.8, cex=0.8, bg.margin=\"lightblue\",bg.figure=\"lightyellow\")
					process.capability(obj,spec.limits=c(lsl,usl))
					dev.off()
					# Clean objects. must be cleaned otherwise the subsquent script won't work properly.
					# it only generate first chart, rest of them are empty image.
					detach(limits)
					rm(limits)
					detach(datasource)
					rm(datasource)
					#detach(ChartTitle)
					rm(ChartTitle)
					rm(obj)
					rm(t)
					rm(qcc)
				 ";
	return $RCmd;
}

#############################################################
sub GenHtmlFile {
	my $self                = shift;
	my $WorkingDireRef      = shift;
	my $buildsummaryAoAref  = shift;
	my $ECbreakdownAoAref   = shift;
	my $Charts_PNGFileA_Ref = shift;
	my $nvPlotAoARef        = shift;
	my $valueAoAref         = shift;
	my $OutPutFileRef       = shift;

	# some useful options (see below for full list)
	my $config = {

		#INCLUDE_PATH => './webpagetemplate',    # or list ref
		INCLUDE_PATH => $$WorkingDireRef . '/webpagetemplate',    # or list ref
		INTERPOLATE => 1,           # expand "$var" in plain text
		POST_CHOMP  => 1,           # cleanup whitespace
		PRE_PROCESS => 'header',    # prefix each template
		EVAL_PERL   => 1,           # evaluate Perl code blocks
	};

	# create Template object
	my $tt = Template->new($config);

	# define template variables for replacement
	my $vars = {
		nvplot       => $nvPlotAoARef,
		records      => $valueAoAref,
		ECbreakdown  => $ECbreakdownAoAref,
		charts       => $Charts_PNGFileA_Ref,
		buildsummary => $buildsummaryAoAref,

		#var3  	=> \@list,
		#var4  	=> \&code,
		#var5  	=> $object,
	};
	if ( defined($Charts_PNGFileA_Ref) ) {
		print "\$Charts_PNGFileA_Ref = $Charts_PNGFileA_Ref \n";
	}

	# specify input filename, or file handle, text reference, etc.
	my $input = 'datasummary.tt';

	my $output = $$OutPutFileRef;

	#print "html output file: $outputfile	\n";
	print "html output file: $$OutPutFileRef	\n";

	#$html_file_path=$output;

	#print "the html file path: $html_file_path. \n";
	# process input template, substituting variables
	$tt->process( $input, $vars, $output ) || die $tt->error();

	print "The processing is done!\n";
}

sub return_unique {
	my $self  = shift;
	my @array = @_;
	my %count;
	map { $count{$_} = 1 } @array;
	return sort keys(%count);
}

sub get_TableName() {
	my $self        = shift;
	my $tableNameID = shift;

	my %TBL_name = (
		"General_data" => "dbo.TBL_GENERAL_DATA",
		"SFT_GSM_ALL"  => "dbo.TBL_SFT_GSM_ALLB_ALLC_DATA"
	);

	# my $tmp = $TBL_name{"$tableNameID"};

	return $TBL_name{"$tableNameID"};
}

sub get_DBName() {
	my $self            = shift;
	my $DBServerNameRef = shift;
	my $DBNameID        = shift;

	return $DB_name{$$DBServerNameRef}->{$DBNameID};
}

sub get_SQLScriptForAllTestResult() {
	my $self               = shift;
	my $filterRef = shift;
	
	my $tableName      = shift;
	my $whereStatement = get_WHERE_subScript( $filterRef );
	return sprintf( "SELECT * 
					FROM %s
    				%s ",
					$tableName,
					$whereStatement
	);

}

sub get_SQLScriptForRunID() {
	my $self = shift;

	my $column = shift;
	my $table  = shift;
	my $runID  = shift;

	my $sqlScript = sprintf "	SELECT %s
								FROM %s
								WHERE G_Test_RunID like '%s'", $column, $table, $runID;
	return $sqlScript;

}

sub get_SQLScriptForCurrentRunID() {
	my $self = shift;

	my $column = shift;
	my $table  = shift;
	my $runID  = shift;

	my $sqlScript = sprintf " SELECT %s
	                          FROM %s
	                          WHERE RunID like '%s'", $column, $table, $runID;
	return $sqlScript;

}

sub get_SQLScriptforAllMeas() {
	my $self = shift;

	my $tableRef           = shift;
	my $colListRef         = shift;
	my $runIDListRef       = shift;
	my $stationtypeRef     = shift;
	my $TargetstationIDRef = shift;
	my $productIDRef       = shift;
	my $productBOMRef      = shift;
	my $productFWRef       = shift;
	my $testcodeversionRef = shift;
	my $nthResult          = shift;
	my $tableToGetRunID    = shift;

	# my @tmp = map {"\'".$_."\'"} @$colListRef;
	my $columnList = join ",", @$colListRef;

	# my $runIDList	= join ",", map {"\'".$_."\'"} @$runIDListRef;
	my $runIDList = get_SQLScriptColOfNthRun(
		$tableRef,       $tableToGetRunID,    $nthResult,
		$stationtypeRef, $TargetstationIDRef, $productIDRef,
		$productBOMRef,  $productFWRef,       $testcodeversionRef
	);
	my $script = sprintf(
		"select %s from %s
		where G_Test_RunID in (%s)",
		$columnList,
		$$tableRef,
		$runIDList
	);

	return $script;
}

sub get_SQLScriptforMeasOfOneRunID() {
	my $self           = shift;
	my $tableRef       = shift;
	my $colListRef     = shift;
	my $runID          = shift;
	my $colNameOfRunID = shift;

	my @tmp = map { "\"" . $_ . "\"" } @$colListRef;

	# my $columnList 	= join ",", @$colListRef;
	my $columnList = join ",", @tmp;

	my $script = sprintf "	select %s  from  %s
						 	where  %s like '%s\'", 
						 	$columnList, 
						 	$$tableRef, 
						 	$colNameOfRunID, 
						 	$runID;

	return $script;
}

sub get_SQLScriptDataType() {

	my $self     = shift;
	my $tableRef = shift;

	my $script = sprintf(
		"	select * from INFORMATION_SCHEMA.COLUMNS
         	where TABLE_NAME like '%s'  
			and   DATA_TYPE like 'decimal'",
		$$tableRef
	);
	return \$script;

}

sub get_SQLScriptForNthTestResult() {
	my $self		= shift;
	my $filterRef 	= shift;
	my $NthTestResult = shift;
	my $tableName     = shift;

	my $whereStatement = get_WHERE_subScript( $filterRef );

	return sprintf( "SELECT * FROM (	SELECT ROW_NUMBER() OVER (PARTITION BY G_FSN ORDER BY DB_Primary_Key ASC) rn,
        			-- DB_Date_Time_Inserted, DB_Primary_Key, G_FSN, G_Station_Type
					*
    				FROM %s
    				%s
					) x WHERE rn = %s",

		$tableName,
		$whereStatement,
		$NthTestResult
	);

}
sub get_WHERE_subScript {
	# why is this? otherwise, it won't work in getDateRange(). other cases are OK. ?
	# http://www.perlmonks.org/?node_id=208898
	my $filterRef = shift;
	
	my $script = sprintf( "WHERE 	G_Station_Type		like '%s'
					AND G_Station_SubType		like '%s'
					AND G_Station_Number 		like '%s'
					AND G_DUT_Type 				like '%s'
					AND G_FSN_BOM				like '%s'
					AND G_Firmware_Version		like '%s'
					AND G_App_Software_Version	like '%s'
					AND DB_Date_Time_Inserted BETWEEN '%s 00:00:00' AND '%s 23:59:59'",
		$filterRef->{"stnType"},
		$filterRef->{"stnSubType"},
		$filterRef->{"stnID"},
		$filterRef->{"prodID"},
		$filterRef->{"prodBOM"},
		$filterRef->{"prodFW"},
		$filterRef->{"testATEVer"},
		$filterRef->{"startDate"},
		$filterRef->{"endDate"}
	);
	
	return $script;
	
}


sub getDateRange {
	my $self 		= shift;
	my $dbh			= shift;
	my $filterRef 	= shift;

	# http://www.perlmonks.org/?node_id=208898	
	my $whereState	= get_WHERE_subScript($filterRef);
		
	# get start date
	my $dt_start = DateTime->now;
	my $sql_stmt	= sprintf "	SELECT min (DB_Date_Time_Inserted)
						FROM dbo.TBL_GENERAL_DATA
						%s",
						$whereState;
												
	my $arrayRef = run_query(undef, $dbh, $sql_stmt);
	print "arrayref[0][0]: $arrayRef->[0]->[0]";
	my @tmp = $arrayRef->[0]->[0] =~ m/(\d+)-(\d+)-(\d+)/g;
	$dt_start->set_year($tmp[0]);
	$dt_start->set_month($tmp[1]);
	$dt_start->set_day($tmp[2]);

	
	# get end date
	my $dt_end = DateTime->now;
	$sql_stmt	= sprintf "	SELECT max (DB_Date_Time_Inserted)
							FROM dbo.TBL_GENERAL_DATA
							%s",
							$whereState;
							
	$arrayRef = run_query(undef, $dbh, $sql_stmt);
	@tmp = $arrayRef->[0]->[0] =~ m/(\d+)-(\d+)-(\d+)/g;
	$dt_end ->set_year($tmp[0]);
	$dt_end ->set_month($tmp[1]);
	$dt_end ->set_day($tmp[2]);

	return ($dt_start , $dt_end);
}


sub disconnDB() {
	my $self  = shift;
	my $dbRef = shift;
	$dbRef->disconnect() if ( ref($dbRef) );

}




1;
__END__
# Below is stub documentation for your module. You'd better edit it!

=head1 NAME

utility - Perl extension for blah blah blah

=head1 SYNOPSIS

  use utility;
  blah blah blah

=head1 DESCRIPTION

Stub documentation for utility, created by h2xs. It looks like the
author of the extension was negligent enough to leave the stub
unedited.

Blah blah blah.

=head2 EXPORT

None by default.



=head1 SEE ALSO

Mention other useful documentation such as the documentation of
related modules or operating system documentation (such as man pages
in UNIX), or any relevant external documentation such as RFCs or
standards.

If you have a mailing list set up for your module, mention it here.

If you have a web site set up for your module, mention it here.

=head1 AUTHOR

A. U. Thor, E<lt>a.u.thor@a.galaxy.far.far.awayE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2011 by A. U. Thor

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.12.2 or,
at your option, any later version of Perl 5 you may have available.


=cut
